package cn.enilu.guns.dao.door;


import cn.enilu.guns.bean.dto.EventRecordDto;
import cn.enilu.guns.bean.entity.door.EventRecord;
import cn.enilu.guns.dao.BaseRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import javax.persistence.NamedNativeQuery;
import java.util.List;


public interface EventRecordRepository extends BaseRepository<EventRecord,Long>{

//    @Query(nativeQuery = true)
//    Page<EventRecordDto> queryEventRecordDto(Long userId, Pageable pageable);



//    @Query(value="SELECT new cn.enilu.guns.bean.dto.EventRecordDto(" +
//            " PIN," +
//            " CARD_NO," +
//            " ''," +
//            " ''," +
//            " VERIFIED," +
//            " DOOR_ID," +
//            " ''," +
//            " EVENT_TYPE," +
//            " ''," +
//            " ''," +
//            " INOUT_STATE," +
//            " TIME_SECOND," +
//            " inoutfun_Index," +
//            " SITE_CODE," +
//            " EQUIPMENT_ID," +
//            " '')" +
//            " from t_event_record a" +
//            " where (pin = ?1 OR ?1 IS NULL)" +
//            " ORDER BY ?2",
//            countQuery = "SELECT COUNT(a) FROM " +
//                    " from t_event_record  " +
//                    " where (pin = ?1 OR ?1 IS NULL)")
//    Page<EventRecordDto> queryEventRecordDto(Long userId, Pageable pageable);

    @Query(nativeQuery = true,value="SELECT " +
            " a.id id," +
            " a.PIN pin," +
            " a.CARD_NO cardNo," +
            " u.NAME userName," +
            " d.FULLNAME deptName," +
            " a.VERIFIED verified," +
            " a.DOOR_ID doorId," +
            " di.door_name doorName," +
            " a.EVENT_TYPE eventType," +
            " et.event_name eventName," +
            " et.event_status eventStatus," +
            " a.INOUT_STATE inoutState," +
            " a.TIME_SECOND timeSecond," +
            " a.inoutfun_Index inoutfunIndex," +
            " a.SITE_CODE siteCode," +
            " a.EQUIPMENT_ID equipmentId," +
            " e.EQUIPMENT_NAME equipmentName," +
            " a.area_name areaName," +
            " a.VERIFIED_NAME verifiedName" +
            " from t_event_record a" +
            " LEFT JOIN t_sys_user u on a.pin = u.ID " +
            " LEFT JOIN t_sys_dept d on u.deptId = d.ID " +
            " LEFT JOIN t_door_info di on a.DOOR_ID = di.door_no and a.equipment_id = di.equipment_id " +
            " LEFT JOIN t_equipment_info e on a.equipment_id = e.ID " +
            " LEFT JOIN t_event_type et on a.EVENT_TYPE = et.Event_code" +
            " where (pin = ?1 OR ?1 IS NULL)" +
            "   and (e.EQUIPMENT_NAME = ?2 OR ?2 IS NULL)" +
            "   and (PARSEDATETIME(a.TIME_SECOND, 'yyyy-MM-dd') >= ?3 OR ?3 IS NULL)" +
            "   and (PARSEDATETIME(a.TIME_SECOND, 'yyyy-MM-dd') <= ?4 OR ?4 IS NULL)" +
            "   and (et.EVENT_STATUS = ?5 OR ?5 IS NULL)" ,
            countQuery = "SELECT COUNT(1) FROM (" +
                    " SELECT " +
                    " PIN pin" +
                    " from t_event_record a" +
                    " LEFT JOIN t_equipment_info e on a.equipment_id = e.ID " +
                    " LEFT JOIN t_event_type et on a.EVENT_TYPE = et.Event_code" +
                    " where (pin = ?1 OR ?1 IS NULL)" +
                    "   and (e.EQUIPMENT_NAME = ?2 OR ?2 IS NULL)" +
                    "   and (PARSEDATETIME(a.TIME_SECOND, 'yyyy-MM-dd') >= ?3 OR ?3 IS NULL)" +
                    "   and (PARSEDATETIME(a.TIME_SECOND, 'yyyy-MM-dd') <= ?4 OR ?4 IS NULL)" +
                    "   and (et.EVENT_STATUS = ?5 OR ?5 IS NULL)) er")
    Page<EventRecordDto> queryEventRecordDto(Long userId,String equipmentName,String beginTime,String endTime,String eventStatus, Pageable pageable);


    //    @Query(value="SELECT new cn.enilu.guns.bean.dto.EventRecordDto(" +
//            " PIN pin," +
//            " CARD_NO cardNo," +
//            " '' userName," +
//            " '' deptName," +
//            " VERIFIED verified," +
//            " DOOR_ID doorId," +
//            " '' doorName," +
//            " EVENT_TYPE eventType," +
//            " '' eventName," +
//            " '' eventStatus," +
//            " INOUT_STATE inoutState," +
//            " TIME_SECOND timeSecond," +
//            " inoutfun_Index inoutfunIndex," +
//            " SITE_CODE siteCode," +
//            " EQUIPMENT_ID equipmentId," +
//            " '' equipmentName)" +
//            " from t_event_record a" +
//            " where (pin = ?1 OR ?1 IS NULL)" +
//            " ORDER BY ?#{#pageable}",
//            countQuery = "SELECT COUNT(1) FROM (" +
//                    " SELECT " +
//                    " PIN pin" +
//                    " where (pin = ?1 OR ?1 IS NULL)" +
//                    " ORDER BY ?#{#pageable})")
//    Page<EventRecordDto> queryEventRecordDto(Long userId, Pageable pageable);
}

